<?php
require_once '../classes/PHPExcel/IOFactory.php';
require_once '../classes/database.php';
require_once '../classes/supervisor.php';
require_once '../classes/subject.php';
require_once '../classes/tools.php';

//
//$a = new ExcelAnalyser('upload/time_table.xlsx');
//$a->update_worksheets();
//$b = $a->get_detail();

/**
 * This class would read an excel file provided by the constructor.
 * Then its sheets would be analysed and databases would be updated
 * according to the sheet.
 * 
 * Inconsistancies may presist in 
 * 
 * @category SASE
 * @package SASE
 */ 
class ExcelAnalyser {
    
	private $file_name = "";
	private $objReader;
	private $objPHPExcel;
	private $worksheets;
    
    private $supid_supervisors = array();
    private $supid_defaultsupervisors = array();
    
    private $broken_supid_list = array();
	
	public function __construct($file_name) {
		//would require to check if the file exists or not
		$this->file_name = $file_name;
		if (!file_exists($file_name)) exit("NO FILE"); 
		$this->objReader = PHPExcel_IOFactory::createReader('Excel2007');
		$this->objPHPExcel = $this->objReader->load($file_name);
        //echo 'filename '.$file_name;
        //Update details about worksheets
        $this->update_worksheets();
	}
	
    public function process() {
        $this->generate_subject_detail();
        $this->generate_supervisor_detail();
        $this->generate_defaultsupervisor_detail();
    }
    
	public function update_worksheets() {
		$this->worksheets = array(); 
		foreach ($this->objPHPExcel->getWorksheetIterator() as $worksheet)
			$this->worksheets[] = $worksheet;
	}
	
    /**
     * This method, needs to be run after update_worksheets() method
     * which will load the different sheets to an array $this->worksheets.
     * Then this method will read the sheet and return an associative array
     * dimensions as of the excel work sheet, which is a 2D array.
     * 
     * @param $sheet_no The index of the sheet, starts with 0
     * @return $content Associative array containing information about the relevent sheet
     */ 
	private function get_detail($sheet_no = 0) {
	   
		$content = array();
        $worksheet = $this->worksheets[$sheet_no];
        
        foreach ($worksheet->getRowIterator() as $row) {
            
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
                
                //Store each row in to an array
				$content_row = array();
                foreach ($cellIterator as $cell) {
                    
                    if (!is_null($cell)) {
                    
                        $col_name = explode ($row->getRowIndex() . "", $cell->getCoordinate());
                        $content_row[ $col_name[0] ] = $cell->getCalculatedValue();
    			    
                     }
                     
				}
         		//Add to the 2D array to have data of the sheet       
				$content[] = $content_row;
		}
        return $content;
	}
    /**
     * This function will update the sase_subject table by the information available at
     * the 1st sheet.
     * 
     * It is ncessary to maintain the sase_subject table empty by the time this function is called.
     * 
     */ 
    function generate_subject_detail() {echo 'processing subject';
        
        $this->clear_table("sase_subject");
        
        $content = $this->get_detail(0);
        $i = 0;
 /**
        $sql = "insert into sase_subject (sub_code, sub_name, sub_stdcount, sub_day, sub_start, sub_end, sub_available) values ";
        
        
 *      foreach ($content as $row) {
 *             if ($i == 0) {
 *                 //Bipass the first row, its the table title    
 *             }
 *             else {
 *                 
 *                 $sql = $sql . "("
 *                     . "'" . $row['A'] . "' , "
 *                     . "'" . $row['B'] . "' , "
 *                     . " " . $row['C'] . "  , "
 *                     . "'" . $row['D'] . "' , "
 *                     . "'" . $row['E'] . "' , "
 *                     . "'" . $row['F'] . "' , "
 *                     . " " . $row['G'] . " ) ,";
 *             }
 *             $i++;
 *         }
 *         
 *         $sql = substr($sql, 0, strlen($sql) - 2);
 *         
 *         //echo '<br>Processed SQL '. $sql . 'End of SQL <br>';
 *         $dbo = Database::getDBOInstance();
 *         $result = $dbo->mysql_query($sql);
 */
        
        foreach ($content as $row) { echo 'here <br>';
            
            //Evading the first row
            if ($i != 0){ 
        
                Subject::addSubject($row['A'], $row['B'], $row['C'], $row['D'], $row['E'], $row['F'], $row['G'], 'abc');
                
            }
            $i++;            
        }
            
    }
    
    /**
     * This method will generate the necessary sql by analysing the content red by the class
     * and will add them to the database.
     * 
     * It is necessary to keep the table, sase_supervisor empty by the time this function
     * is called.
     */ 
    function generate_supervisor_detail() {
        
        $this->clear_table("sase_supervisor");
        
        $content = $this->get_detail(1);
        
        $i = 0;
        /**

 *         //$sql_tmplate = "insert into sase_supervisor (sup_id, sup_name, sup_pwd, sup_department, sup_available) values ('[id]', '[name]', '[pwd]', '[department]', [available])";
 *         $sql = "insert into sase_supervisor (sup_id, sup_name, sup_pwd, sup_department, sup_available) values ";
 *         
 *         foreach ($content as $row) {
 *             //print_r($row);
 *             if ($i == 0) {
 *                 //Bipass the first row, its the table title    
 *             }
 *             else {
 *                 
 *                 $sql = $sql . "("
 *                     . "'" . $row['B'] . "' , "
 *                     . "'" . $row['A'] . "' , "
 *                     . "'" . generate_password() . "', "
 *                     . "'" . $row['C'] . "' , "
 *                     . "'" . $row['D'] . "' ) ,"
 *                     ;
 *                 
 *                 $this->supid_supervisors[] = $row['A'];
 *                 
 *                 //$sql = $sql . $sql_tmplate;
 *                 
 *             }    
 *             $i++;
 *         }
 *         $sql = substr($sql, 0, strlen($sql) - 2);
 *         $dbo = Database::getDBOInstance();
 *         $result = $dbo->mysql_query($sql);
 */
        
        foreach ($content as $row) {
            
            if ($i != 0) {
                
                Supervisor::addSupervisor($row['B'], $row['A'], $row['C'], $row['D']);
                    
            }
            $i++;
            
        }
    }
    
    /**
     * This method will generate the necessary sql by analysing the content red by the class
     * and will add them to the database.
     * 
     * It is necessary to keep the table, default_supervisor empty by the time this function
     * is called.
     */ 
    function generate_defaultsupervisor_detail() {
        
        //Clear the table;
        $this->clear_table("default_supervisor");
        
        $content = $this->get_detail(2);
        $i = 0;
        /**

 *         //$sql_tmplate = "insert into default_supervisor (sup_id, sub_code) values ('$id', '$code')";
 *         $sql = "insert into default_supervisor (sup_id, sub_code) values ";
 *         $sql2= "insert into supervises_subject (sup_id, sub_code) values ";
 *         foreach ($content as $row) {
 *             if ($i == 0) {
 *                 //Bipass the first row, its the table title    
 *             }
 *             else {
 *                 //
 *                 //  Content only exists in 2 cols, the first will tell the supervisor id or email
 *                 //  while the second will tell the subject code that he/she is supervising. 
 *                 //
 *                 $id = $content['B'];
 *                 $code = $content['A'];
 *                 
 *                 $this->supid_defaultsupervisors[] = $id;
 *                 
 *                 $sql = $sql . "("
 *                     . "'" . $row['B'] . "' , "
 *                     . "'" . $row['A'] . "' ) ,";
 *                     
 *                 $sql2 = $sql2 . "("
 *                     . "'" . $row['B'] . "' , "
 *                     . "'" . $row['A'] . "' ) ,";
 *                         
 *             }
 *             $i++;
 *         }
 *         
 *         $sql = substr($sql, 0, strlen($sql) - 2);
 *         $sql2 = substr($sql2, 0, strlen($sql2) - 2);
 *         $dbo = Database::getDBOInstance();
 *         $result = $dbo->mysql_query($sql);
 *         
 *         //Copy this data to supervises_exam
 *         $dbo->mysql_query($sql2);
 */
        
        foreach ($content as $row) {
            
            if ($i != 0) {
                Supervisor::enrolDefaultSupervisor($row['A'], $row['B']);
            }
            $i ++;
               
        }
        
    }
    
    public function get_inconsistancy() {
        foreach ($this->supid_defaultsupervisors as $ds) {
            if ( array_search($ds, $this->supid_supervisors) === FALSE ) {
                $this->broken_supid_list[] = $ds;
            }
        }
        
        return $this->broken_supid_list;
    }

    private function clear_table($table_name) {
        $dbo = Database::getDBOInstance();
        $query = "delete from $table_name";
        $dbo->mysql_query($query);
        //echo $query;
    }
    
            	
}

?>